﻿Imports DTO
Imports System.Linq

Public Class HoSoDAO

    Public Sub addProfile_DAO(ByVal st As HoSoHSDTO)
        Dim context As DataproviderDataContext = New DataproviderDataContext()
        Dim st1 = New HoSoHS()

        st1.MaHoSo = st.MaHoSo
        st1.MaHS = st.MaHS
        st1.MaLop = st.MaLop
        st1.TinhTrang = st.TinhTrang

        context.HoSoHS.InsertOnSubmit(st1)
        context.SubmitChanges()
    End Sub

    'Tim hoc sinh cua bang tra cuu hoc sinh, lay ra nhung hoc sinh da co trong ds diem thi, lay ra diem trung binh cua hoc sinh trong tung hoc ky
    Public Function findStudent_DAO(ByVal stname As String, ByVal address As String, ByVal mail As String, ByVal borndate As Date, ByVal sex As Integer, ByVal mak As Integer, ByVal mal As String, ByVal year As Integer, ByVal matt As Integer) As DataTable
        Dim dt As DataTable = New DataTable()
        dt.Columns.Add("MaHoSo")
        dt.Columns.Add("HoTen")
        dt.Columns.Add("NgaySinh")
        dt.Columns.Add("GioiTinh")
        dt.Columns.Add("DiaChi")
        dt.Columns.Add("Email")
        dt.Columns.Add("Lop")
        dt.Columns.Add("TBHK1")
        dt.Columns.Add("TBHK2")
        dt.Columns.Add("TT")
        dt.Columns.Add("MaHS")
        dt.Columns.Add("NamHoc")
        dt.Columns.Add("MaKhoi")

        Dim context As DataproviderDataContext = New DataproviderDataContext()

        Dim listst

        Dim birthday As Integer
        If borndate = Date.Today() Then
            birthday = 0 ' ngày bang ngày hiện tại nên k tra cứu theo ngày sinh.
        Else
            birthday = 1 ' tra cuu theo ngay sinh
        End If
        If mak = 0 And mal = "" And matt = 0 And birthday = 0 And sex = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hocky.NamHoc = year _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        ElseIf mak = 0 And mal = "" And matt = 0 And birthday = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hocky.NamHoc = year _
                     And hocsinh.GioiTinh = sex _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        ElseIf mak = 0 And mal = "" And matt = 0 And sex = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hocky.NamHoc = year _
                     And hocsinh.NgaySinh.Date = borndate _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        ElseIf mak = 0 And matt = 0 And birthday = 0 And sex = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hocky.NamHoc = year _
                     And hoso.MaLop.Contains(mal) _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        ElseIf mak = 0 And mal = "" And sex = 0 And birthday = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hocky.NamHoc = year _
                     And hoso.TinhTrang = matt _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        ElseIf mak = 0 And mal = "" And matt = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hocky.NamHoc = year _
                     And hocsinh.NgaySinh.Date = borndate _
                     And hocsinh.GioiTinh = sex _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        ElseIf mak = 0 And mal = "" And sex = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hocky.NamHoc = year _
                     And hoso.TinhTrang = matt _
                     And hocsinh.NgaySinh.Date = borndate _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        ElseIf mak = 0 And mal = "" And birthday = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hocky.NamHoc = year _
                     And hoso.TinhTrang = matt _
                     And hocsinh.GioiTinh = sex _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        ElseIf mak = 0 And birthday = 0 And sex = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hocky.NamHoc = year _
                     And hoso.TinhTrang = matt _
                     And hoso.MaLop.Contains(mal) _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        ElseIf mak = 0 And matt = 0 And birthday = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hocky.NamHoc = year _
                     And hoso.MaLop.Contains(mal) _
                     And hocsinh.GioiTinh = sex _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        ElseIf matt = 0 And birthday = 0 And sex = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.MaLop.Contains(mal) _
                     And l.MaKhoi = mak _
                     And hocky.NamHoc = year _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        ElseIf mak = 0 And mal = "" Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hocky.NamHoc = year _
                     And hoso.TinhTrang = matt _
                     And hocsinh.NgaySinh.Date = borndate _
                     And hocsinh.GioiTinh = sex _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        ElseIf mak = 0 And matt = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hocky.NamHoc = year _
                     And hoso.MaLop.Contains(mal) _
                     And hocsinh.GioiTinh = sex _
                     And hocsinh.NgaySinh.Date = borndate _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        ElseIf mak = 0 And birthday = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hocky.NamHoc = year _
                     And hoso.TinhTrang = matt _
                     And hoso.MaLop.Contains(mal) _
                     And hocsinh.GioiTinh = sex _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        ElseIf mak = 0 And sex = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hocky.NamHoc = year _
                     And hoso.TinhTrang = matt _
                     And hoso.MaLop.Contains(mal) _
                     And hocsinh.NgaySinh.Date = borndate _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        ElseIf matt = 0 And birthday = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.MaLop.Contains(mal) _
                     And l.MaKhoi = mak _
                     And hocky.NamHoc = year _
                     And hocsinh.GioiTinh = sex _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        ElseIf mak = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.MaLop.Contains(mal) _
                     And hocky.NamHoc = year _
                     And hoso.TinhTrang = matt _
                     And hocsinh.NgaySinh.Date = borndate _
                     And hocsinh.GioiTinh = sex _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        ElseIf matt = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.MaLop.Contains(mal) _
                     And l.MaKhoi = mak _
                     And hocky.NamHoc = year _
                     And hocsinh.GioiTinh = sex _
                     And hocsinh.NgaySinh.Date = borndate _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group
        Else
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                     Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.MaLop.Contains(mal) _
                     And l.MaKhoi = mak _
                     And hocky.NamHoc = year _
                     And hocsinh.GioiTinh = sex _
                     And hocsinh.NgaySinh.Date = borndate _
                     And hoso.TinhTrang = matt _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group
        End If

        Dim dmh As DiemMHDAO = New DiemMHDAO()

        For Each value In listst
            Dim row As DataRow = dt.NewRow()
            row.Item(0) = value.MaHoSo
            row.Item(1) = value.TenHS
            row.Item(2) = value.NgaySinh

            If value.GioiTinh = 1 Then
                row.Item(3) = "Nam"
            Else
                row.Item(3) = "Nữ"
            End If

            row.Item(4) = value.DiaChi
            row.Item(5) = value.Email
            row.Item(6) = value.TenLop

            Dim avg1 As Double = dmh.getScore_DAO(value.MaHoSo, 1, year)
            Dim avg2 As Double = dmh.getScore_DAO(value.MaHoSo, 2, year)

            row.Item(7) = avg1
            row.Item(8) = avg2

            row.Item(9) = value.LoaiTT
            row.Item(10) = value.MaHS
            row.Item(11) = value.NamHoc
            row.Item(12) = value.MaKhoi

            dt.Rows.Add(row)
        Next

        Return dt
    End Function

    Public Sub UpdateProfileStudent_DAO(ByVal mahs As Integer, ByVal mak As Integer, ByVal mal As String, ByVal matt As Integer)
        Dim context As DataproviderDataContext = New DataproviderDataContext()

        Dim list = From hoso In context.HoSoHS
                   Where hoso.MaHoSo = mahs

        For Each value In list
            value.MaLop = mal
            value.TinhTrang = matt
        Next
        context.SubmitChanges()

    End Sub

    Public Function getProfile_DAO() As DataTable
        Dim dt As DataTable = New DataTable
        dt.Columns.Add("MaHoSo")
        dt.Columns.Add("HoTen")
        dt.Columns.Add("NgaySinh")
        dt.Columns.Add("GioiTinh")
        dt.Columns.Add("DiaChi")
        dt.Columns.Add("Email")
        dt.Columns.Add("Lop")
        dt.Columns.Add("TBHK1")
        dt.Columns.Add("TBHK2")
        dt.Columns.Add("TT")
        dt.Columns.Add("MaHS")
        dt.Columns.Add("NamHoc")
        dt.Columns.Add("MaKhoi")

        Dim context As DataproviderDataContext = New DataproviderDataContext()

        Dim list = From hocsinh In context.HocSinhs
                   Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                   Join l In context.Lops On hoso.MaLop Equals l.MaL
                   Join diem In context.DiemMHs On hoso.MaHoSo Equals diem.MaHoSo
                   Join hocky In context.HocKies On diem.MaHK Equals hocky.MaHK
                   Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                   Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT, hocky.NamHoc, l.MaKhoi
                   Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT, NamHoc, MaKhoi Into Group

        Dim dmh As DiemMHDAO = New DiemMHDAO()
        For Each value In list
            Dim row As DataRow = dt.NewRow()
            row.Item(0) = value.MaHoSo
            row.Item(1) = value.TenHS
            row.Item(2) = value.NgaySinh

            If value.GioiTinh = 1 Then
                row.Item(3) = "Nam"
            Else
                row.Item(3) = "Nữ"
            End If

            row.Item(4) = value.DiaChi
            row.Item(5) = value.Email
            row.Item(6) = value.TenLop

            Dim avg1 As Double = dmh.getScore_DAO(value.MaHoSo, 1, value.NamHoc)
            Dim avg2 As Double = dmh.getScore_DAO(value.MaHoSo, 2, value.NamHoc)

            row.Item(7) = avg1
            row.Item(8) = avg2

            row.Item(9) = value.LoaiTT
            row.Item(10) = value.MaHS
            row.Item(11) = value.NamHoc
            row.Item(12) = value.MaKhoi

            dt.Rows.Add(row)
        Next

        Return dt
    End Function

    Public Function findProfile_DAO(ByVal stname As String, ByVal address As String, ByVal mail As String, ByVal borndate As Date, ByVal sex As Integer, ByVal mak As Integer, ByVal mal As String, ByVal matt As Integer) As DataTable
        Dim dt As DataTable = New DataTable()
        dt.Columns.Add("MaHoSo")
        dt.Columns.Add("HoTen")
        dt.Columns.Add("NgaySinh")
        dt.Columns.Add("GioiTinh")
        dt.Columns.Add("DiaChi")
        dt.Columns.Add("Email")
        dt.Columns.Add("Lop")
        dt.Columns.Add("TBHK1")
        dt.Columns.Add("TBHK2")
        dt.Columns.Add("TT")
        dt.Columns.Add("MaHS")

        Dim context As DataproviderDataContext = New DataproviderDataContext()

        Dim listst

        Dim birthday As Integer
        If borndate = Date.Today() Then
            birthday = 0
        Else
            birthday = 1
        End If

        If mak = 0 And mal = "" And matt = 0 And birthday = 0 And sex = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group

        ElseIf mak = 0 And mal = "" And matt = 0 And birthday = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hocsinh.GioiTinh = sex _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group

        ElseIf mak = 0 And mal = "" And matt = 0 And sex = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hocsinh.NgaySinh.Date = borndate _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group

        ElseIf mak = 0 And matt = 0 And birthday = 0 And sex = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.MaLop.Contains(mal) _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group

        ElseIf mak = 0 And mal = "" And sex = 0 And birthday = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.TinhTrang = matt _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group

        ElseIf mak = 0 And mal = "" And matt = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hocsinh.NgaySinh.Date = borndate _
                     And hocsinh.GioiTinh = sex _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group

        ElseIf mak = 0 And mal = "" And sex = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.TinhTrang = matt _
                     And hocsinh.NgaySinh.Date = borndate _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group

        ElseIf mak = 0 And mal = "" And birthday = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.TinhTrang = matt _
                     And hocsinh.GioiTinh = sex _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group

        ElseIf mak = 0 And birthday = 0 And sex = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.TinhTrang = matt _
                     And hoso.MaLop.Contains(mal) _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group

        ElseIf mak = 0 And matt = 0 And birthday = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.MaLop.Contains(mal) _
                     And hocsinh.GioiTinh = sex _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group

        ElseIf matt = 0 And birthday = 0 And sex = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.MaLop.Contains(mal) _
                     And l.MaKhoi = mak _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group

        ElseIf mak = 0 And mal = "" Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.TinhTrang = matt _
                     And hocsinh.NgaySinh.Date = borndate _
                     And hocsinh.GioiTinh = sex _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group

        ElseIf mak = 0 And matt = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.MaLop.Contains(mal) _
                     And hocsinh.GioiTinh = sex _
                     And hocsinh.NgaySinh.Date = borndate _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group

        ElseIf mak = 0 And birthday = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.TinhTrang = matt _
                     And hoso.MaLop.Contains(mal) _
                     And hocsinh.GioiTinh = sex _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group

        ElseIf mak = 0 And sex = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.TinhTrang = matt _
                     And hoso.MaLop.Contains(mal) _
                     And hocsinh.NgaySinh.Date = borndate _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group

        ElseIf matt = 0 And birthday = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.MaLop.Contains(mal) _
                     And l.MaKhoi = mak _
                     And hocsinh.GioiTinh = sex _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group

        ElseIf mak = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.MaLop.Contains(mal) _
                     And hoso.TinhTrang = matt _
                     And hocsinh.NgaySinh.Date = borndate _
                     And hocsinh.GioiTinh = sex _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group

        ElseIf matt = 0 Then
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.MaLop.Contains(mal) _
                     And l.MaKhoi = mak _
                     And hocsinh.GioiTinh = sex _
                     And hocsinh.NgaySinh.Date = borndate _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group
        Else
            listst = From hocsinh In context.HocSinhs
                     Join hoso In context.HoSoHS On hocsinh.MaHS Equals hoso.MaHS
                     Join l In context.Lops On hoso.MaLop Equals l.MaL
                     Join tinhtrang In context.TinhTrangs On hoso.TinhTrang Equals tinhtrang.MaTT
                     Where hocsinh.TenHS.Contains(stname) And hocsinh.DiaChi.Contains(address) And hocsinh.Email.Contains(mail) _
                     And hoso.MaLop.Contains(mal) _
                     And l.MaKhoi = mak _
                     And hocsinh.GioiTinh = sex _
                     And hocsinh.NgaySinh.Date = borndate _
                     And hoso.TinhTrang = matt _
                     Select hocsinh.TenHS, hocsinh.MaHS, hocsinh.NgaySinh, hocsinh.DiaChi, hocsinh.GioiTinh, hocsinh.Email, hoso.MaHoSo, l.TenLop, tinhtrang.LoaiTT
                     Group By MaHS, MaHoSo, TenHS, NgaySinh, DiaChi, GioiTinh, Email, TenLop, LoaiTT Into Group
        End If

        Dim dmh As DiemMHDAO = New DiemMHDAO()

        For Each value In listst
            Dim row As DataRow = dt.NewRow()
            row.Item(0) = value.MaHoSo
            row.Item(1) = value.TenHS
            row.Item(2) = value.NgaySinh
            If value.GioiTinh = 1 Then
                row.Item(3) = "Nam"
            Else
                row.Item(3) = "Nữ"
            End If
            row.Item(4) = value.DiaChi
            row.Item(5) = value.Email
            row.Item(6) = value.TenLop
            row.Item(7) = value.LoaiTT
            row.Item(8) = value.MaHS

            dt.Rows.Add(row)
        Next

        Return dt
    End Function
End Class
